<?php declare(strict_types=1);

namespace App\Services\Count;


use App\Enums\ReceiveModelEnum;
use App\Models\Bi\Bill;
use App\Models\Bi\BillLog;

use App\Models\Merchant;

use App\Models\Store;
use App\Models\StoreAllPayWayList;
use App\Models\User;
use App\Services\PrintSqlService;
use App\Util\ExeclUtil;
use App\Util\ListUtil;
use App\Util\TimeUtil;
use App\Util\WhereUtil;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class UserBillCountService
{


    protected function searchTime(array $where): array
    {
        if (!empty($where["curHdIndex"])) {
            $curHdIndex = $where["curHdIndex"];
            $timeUtil = new TimeUtil();
            list($start, $end, $desc) = $timeUtil->get((int)$curHdIndex);
        } else {
            $start = $where["startDate"];
            $end = $where["endDate"];
        }
        if (empty($start) || empty($end)) {
            throw new \Exception("请填写开始时间和结束时间");
        }
        return [$start, $end];
    }


    protected function searchTimeBefore(array $where): array
    {
        $start = $where["startDateBefore"];
        $end = $where["endDateBefore"];
        if (empty($start) || empty($end)) {
            throw new \Exception("请填写开始时间和结束时间");
        }

        if (strtotime($end) <= strtotime($start)) {
            throw new \Exception("开始时间必须大于结束时间");
        }

        if (strtotime($where["startDate"]) < strtotime($start)) {
            throw new \Exception("当前阶段的开始时间必须大于上一个阶段的开始时间");
        }

        if (strtotime($where["endDate"]) < strtotime($end)) {
            throw new \Exception("当前阶段的结束时间必须大于上一个阶段的结束时间");
        }
        return [$start, $end];
    }

//Illuminate\Database\Query\Builder|string $query
//Illuminate\Database\Query\Builder|Illuminate\\Database\\Eloquent\\Builder
    protected function setWhere($query, array $where): void
    {
//        "agentName" => $request->get("agentName"),
//            "storeName" => $request->get("storeName"),
//            "payType" => $request->get("payType"),
//            "company" => $request->get("company"),
//            "money" => $request->get("money"),
//            "cashier" => $request->get("merchant_id"),
//
        $newWhere = WhereUtil::filterWhere($where);
        if (!empty($newWhere)) {
            $query->where($newWhere);
        }

        if (isset($where["rate"])) {
            $query->where("rate", $where["rate"]);
        }

        if (isset($where["mch_no"]) && !empty($where["mch_no"])) {
            if (!(isset($where["company"]) && $where["company"] == "cmbc")) {
                $query->where("mch_no", $where["mch_no"]);
            }
        }

        if (!empty($where["agent1_rate"])) {
            $query->where(function ($query) use ($where) {
                $query->where("agent1_rate", $where["agent1_rate"])->orWhere("oem_rate", $where["agent1_rate"]);
            });
        }


        if (!empty($where["receive_model"])) {
            $query->where("receive_model", $where["receive_model"]);
        }

        if (!empty($where["agent_id"])) {
            //查询代理商
            if (is_array($where["agent_id"])) {
                $query->whereIn("agent_id", $where["agent_id"]);
            } else {
                $query->where("agent_id", $where["agent_id"]);
            }
        }

        if (!empty($where["store_pid"])) {
            if (is_array($where["store_pid"])) {
                $query->whereIn("store_pid", $where["store_pid"]);
            } else {
                $query->where("store_pid", $where["store_pid"]);
            }
        }

        if (!empty($where["store_id"])) {
            if (is_array($where["store_id"])) {
                $query->whereIn("store_id", $where["store_id"]);
            } else {
                $query->where("store_id", $where["store_id"]);
            }
        }

        if (!empty($where["payType"])) {
            $query->where("pay_type", $where["payType"]);
        }

        if (!empty($where["company"])) {
            $query->where("company", $where["company"]);
        }

        if (!empty($where["money"])) {
            $query->where("total_fee", $where["money"]);
        }

        if (!empty($where["cashier_id"])) {
            $query->where("cashier_id", $where["cashier_id"]);
        }


        if (!empty($where["pay_type"])) {
            $query->where("pay_type", $where["pay_type"]);
        }
        if (!empty($where["company"])) {
            $query->where("company", $where["company"]);
        }


        if (!empty($where["oem_id"])) {
            $query->where("oem_id", $where["oem_id"]);
        }

        if (!empty($where["agent1_id"])) {
            $query->where("agent1_id", $where["agent1_id"]);
        }

        if (!empty($where["agent2_id"])) {
            $query->where("agent2_id", $where["agent2_id"]);
        }

        if (!empty($where["agent3_id"])) {
            $query->where("agent3_id", $where["agent3_id"]);
        }

        if (!empty($where["agent4_id"])) {
            $query->where("agent4_id", $where["agent4_id"]);
        }
        if (isset($where["mark"]) && $where["mark"] == 1 && !empty($where["search_store_id"])) {
            $query->where("store_pid", $where["search_store_id"]);
        }
        if (isset($where["mark"]) && $where["mark"] == 0 && !empty($where["search_store_id"])) {
            $query->where("store_id", $where["search_store_id"]);
        }
    }

//\Illuminate\Database\Query\Builder
//Illuminate\Database\Query\Builder|Illuminate\\Database\\Eloquent\\Builder
    protected function setGroup($query, array $group): void
    {
        $tmpGroup = [];
        foreach ($group as $column) {
            if (in_array($column, ["cashier_id", "store_id", "store_pid", "company", "pay_type", "month", "receive_model"])) {
                array_push($tmpGroup, $column);
            }
        }
        if (!empty($tmpGroup)) {
            $query->addSelect($tmpGroup);
            $query->groupBy($tmpGroup);
        }
    }

    //选择强制索引
    protected function switchIndex($query, array $where)
    {
        $query->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`store_pid`)'));
        });
        $query->when(!empty($where["cashier_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`merchant_id`)'));
        });

        $query->when(!empty($where["agent1_id"]) && !empty($where["agent2_id"]) && !empty($where["agent3_id"]) && !empty($where["agent4_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`agent3_id`)'));
        });

        $query->when(!empty($where["agent1_id"]) && !empty($where["agent2_id"]) && !empty($where["agent3_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`agent3_id`)'));
        });

        $query->when(!empty($where["agent1_id"]) && !empty($where["agent2_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`agent2_id`)'));
        });

        $query->when(!empty($where["agent1_id"]), function ($query) {
            $query->from(DB::raw(env("BI_DB_PREFIX", "bi_") . 'bill_logs FORCE INDEX (`agent1_id`)'));
        });
    }

    //收银记录
    public function staticBillLogList(array $where, $pageModel = true, array $column = []): array
    {
//    PrintSqlService::listen();
        list($start, $end) = $this->searchTime($where);
//        $timeBatch = $util->rangeTimeYear($start, $end);
//        $timeMappTables = $util->getTableBill($timeBatch);
        $query = BillLog::query();
        $this->switchIndex($query, $where);
        //在这里设置查询条件
        $query->whereBetween("day", [$start, $end]);
        $this->setWhere($query, $where);
        //嵌套查询
        $total = 0;
        if ($pageModel) {
            $total = $query->count();
        }

        if (!empty($column)) {
            $query->addSelect($column);
        }

        //设置
        $list = $query->addSelect([
            "id", "store_pid", "store_id", "cashier_id", "out_trade_no", "refund_no", "day", "pay_time", "status", "pay_type",
            "total_fee", "fee_amount", "rate", "refund_fee", "receive_model", "refund_type", "company"
        ])
            ->with("refundLog");

        if ($pageModel) {
            $list = $list->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
                ->orderBy("id", "desc")
                ->get()->toArray();
        } else {
            $list = $list
                ->orderBy("id", "desc")
                ->get()->toArray();
        }
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
        $storeIds = array_merge($storeIds, array_column($list, "store_id"));
        $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
        $companys = array_merge($merchantIds, array_column($list, "company"));

        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }


        $receiveModelMap = ReceiveModelEnum::$receiveModelMap;
        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }

            $v["receive_model_name"] = $receiveModelMap[$v["receive_model"]] ?? "";


            $list[$k] = $v;
        }
        return [$list, $total];
    }


    public function staticBillLogListUnionAll(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $timeBatch = $util->rangeTimeMonth($start, $end);
        $timeMappTables = $util->getTableBillLog($timeBatch);
        //在这里判断第一个key
        $firstTableName = array_key_first($timeMappTables);
        $range = $timeMappTables[$firstTableName];
        list($min, $max) = $util->getYearDayRange($range);
        $query = DB::connection("bi")->table($firstTableName);
        $query->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) use ($firstTableName) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $firstTableName . ' FORCE INDEX (`store_pid`)'));
        });
        $query->when(!empty($where["cashier_id"]), function ($query) use ($firstTableName) {
            $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $firstTableName . ' FORCE INDEX (`merchant_id`)'));
        });

        //设置查询条件
        //在这里设置查询条件
//        $query->whereBetween("day", [$min, $max]);
        $query->whereBetween("day", [$min, $max]);
        $this->setWhere($query, $where);
        $select = [
            "id", "store_pid", "store_id", "cashier_id", "out_trade_no", "refund_no", "day", "pay_time", "status", "pay_type",
            "total_fee", "fee_amount", "rate", "refund_fee", "receive_model", "refund_type", "company"
        ];
        $query->addSelect($select);
        //在这里使用union拼接sql
        foreach ($timeMappTables as $table => $range) {
            if ($firstTableName == $table) {
                continue;
            }
            list($min, $max) = $util->getYearDayRange($range);
            $union = DB::connection("bi")->table($table);
            $union->when(!empty($where["store_pid"]) && empty($where["store_id"]), function ($query) use ($table) {
                $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $table . ' FORCE INDEX (`store_pid`)'));
            });
            $union->when(!empty($where["cashier_id"]), function ($query) use ($table) {
                $query->from(DB::raw(env("BI_DB_DATABASE") . '_' . $table . ' FORCE INDEX (`merchant_id`)'));
            });
            //设置查询条件
            //在这里设置查询条件
            $union->whereBetween("day", [$min, $max]);
            $union->addSelect($select);
            $this->setWhere($union, $where);
            $query->unionAll($union);
        }
        $total = BillLog::query()->fromSub($query, "tmp_count")->count();
        //嵌套查询
        $list = $query
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
            ->orderBy("id", "desc")
            ->get()->toArray();
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
        $storeIds = array_merge($storeIds, array_column($list, "store_id"));
        $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
        $companys = array_merge($merchantIds, array_column($list, "company"));

        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }

        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $list[$k] = $v;
        }
        return [$list, $total];
    }


    //每日收款统计  ok
    public function staticDailyGroupCount(array $where)
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $timeBatch = $util->rangeTimeYear($start, $end);
        $timeMappTables = $util->getTableBill($timeBatch);
        $count = [
            "total_fee" => 0,
            "total_num" => 0,
            "refund_fee" => 0,
            "refund_num" => 0,
            "fee_amount" => 0,
            "coupon_amount_mc"=>0,
            "profit_column" => 0,
            "fee_column" => 0,

            "receipt_amount" => 0,//实收  总收款-退款
            "settlement_amount" => 0,//结算 总收款-总退款-手续费

        ];
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);
            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            //支付方式 1：微信 2：支付宝 3：翼支付 4云闪付支付5 刷卡支付
            $query = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(total_num) as total_num"),//交易笔数
                DB::raw("sum(refund_fee) as refund_fee"),//交易笔数
                DB::raw("sum(refund_num) as refund_num"),//交易笔数
                DB::raw("sum(fee_amount) as fee_amount"),//交易笔数
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
                DB::raw("sum(" . $where["profit_column"] . ") as profit_column"),//佣金
                DB::raw("sum(" . $where["fee_column"] . ") as fee_column"),//实收
            ]);
            $result = $query->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["total_num"] = bcadd((string)$count["total_num"], (string)$v["total_num"], 2);//$v["refund_fee"];
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);//$v["fee_amount"];
                $count["refund_num"] = bcadd((string)$count["refund_num"], (string)$v["refund_num"], 2);// $v["refund_fee_nextday"];
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);// $v["refund_fee_nextday"];
                $count["profit_column"] = bcadd((string)$count["profit_column"], (string)$v["profit_column"], 2);
                $count["fee_column"] = bcadd((string)$count["fee_column"], (string)$v["fee_column"], 2);
                $count["receipt_amount"] = bcsub((string)$count["total_fee"], (string)$v["refund_fee"], 2);
                $count["receipt_amount"] = bcsub((string)$count["receipt_amount"], (string)$v["coupon_amount_mc"], 2);
                $count["settlement_amount"] = bcsub(bcsub((string)$count["total_fee"], (string)$v["refund_fee"], 2), (string)$count["fee_amount"], 2);
                $count["settlement_amount"] = bcsub((string)$count["settlement_amount"], (string)$v["coupon_amount_mc"], 2);
            }
        }
        return $count;


    }

    //每日收款分组统计 ok
    public function staticDailyGroupCountList(array $where)
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        $data = [];
        $countTemplate = [
            "total_fee" => 0,
            "total_num" => 0,
        ];
        foreach ($timeMappTables as $table => $times) {
            list($min, $max) = $util->getYearDayRange($times);
            $query = Bill::query()->from($table);
            $query->whereBetween("day", [$min, $max]);
            $this->setWhere($query, $where);
            $storeResults = $query->select([
                "day",
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(total_num) as total_num"), //交易笔数
                //支付宝
            ])
                ->groupBy(["day"])
                ->get()->toArray();

            foreach ($storeResults as $v) {
                $count = $data[$v["day"]] ?? $countTemplate;
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["total_num"] += $v["total_num"];
                $data[$v["day"]] = $count;
            }
        }
        return $data;

    }

    //数据店铺统计  ok
    public function staticStoreGroupCount(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $timeBatch = $util->rangeTimeYear($start, $end);
        $timeMappTables = $util->getTableBill($timeBatch);
        $count = [
            "total_fee" => 0,
            "total_num" => 0,
            "refund_fee" => 0,
            "refund_num" => 0,
            "profit_column" => 0,
            "fee_column" => 0,
            "fee_amount" => 0,
            "coupon_amount_mc" => 0,
            "receipt_amount" => 0,//实收  总收款-退款
            "settlement_amount" => 0,//结算 总收款-总退款-手续费
        ];
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);
            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            //支付方式 1：微信 2：支付宝 3：翼支付 4云闪付支付5 刷卡支付
            $query = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(total_num) as total_num"),//交易笔数
                DB::raw("sum(refund_fee) as refund_fee"),//交易笔数
                DB::raw("sum(refund_num) as refund_num"),//交易笔数
                DB::raw("sum(fee_amount) as fee_amount"),
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
                DB::raw("sum(" . $where["profit_column"] . ") as profit_column"),//佣金
                DB::raw("sum(" . $where["fee_column"] . ") as fee_column"),//实收
            ]);
            $result = $query->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["total_num"] = bcadd((string)$count["total_num"], (string)$v["total_num"], 2);//$v["refund_fee"];
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);//$v["fee_amount"];
                $count["refund_num"] = bcadd((string)$count["refund_num"], (string)$v["refund_num"], 2);// $v["refund_fee_nextday"];
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);// $v["refund_fee_nextday"];
                $count["profit_column"] = bcadd((string)$count["profit_column"], (string)$v["profit_column"], 2);
                $count["fee_column"] = bcadd((string)$count["fee_column"], (string)$v["fee_column"], 2);
                $count["receipt_amount"] = bcsub((string)$count["total_fee"], (string)$v["refund_fee"], 2);
                $count["receipt_amount"] = bcsub((string)$count["receipt_amount"], (string)$v["coupon_amount_mc"], 2);
                $count["settlement_amount"] = bcsub(bcsub((string)$count["total_fee"], (string)$v["fee_amount"], 2), (string)$count["refund_fee"], 2);
                $count["settlement_amount"] = bcsub((string)$count["settlement_amount"], (string)$v["coupon_amount_mc"], 2);
            }
        }
        return $count;
    }

    //数据店铺分组统计
    public function staticStoreGroupCountList(array $where, array $group): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        $query = DB::connection("bi")->table("bills");
        //在这里设置查询条件
        $query->whereBetween("day", [$start, $end]);
        $this->setWhere($query, $where);
        $this->setGroup($query, $group);
        //嵌套查询
        $total = Bill::query()->fromSub($query, "tmp")->count();
        //设置
        $list = $query->addSelect([
//          商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
            DB::raw("sum(fee_amount) as fee_amount"),
//                DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
//                DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
//                DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
//                DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
//                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
//                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
            //查询分润情况
            DB::raw("sum(" . $where["profit_column"] . ") as profit_column"),
            DB::raw("sum(" . $where["fee_column"] . ") as fee_column")
        ])
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])
            ->orderBy("total_fee", "desc")
            ->get()->toArray();
        //处理分组的数据
        $storeIds = [];
        $merchantIds = [];
        $companys = [];
        if (!empty($group)) {
            foreach ($group as $column) {
                if ($column == "store_pid") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_pid"));
                } else if ($column == "store_id") {
                    $storeIds = array_merge($storeIds, array_column($list, "store_id"));
                } else if ($column == "cashier_id") {
                    $merchantIds = array_merge($merchantIds, array_column($list, "cashier_id"));
                } else if ($column == "company") {
                    $companys = array_merge($companys, array_column($list, "company"));
                }
            }
        }
        $storeIds = array_unique($storeIds);
        $merchantIds = array_unique($merchantIds);
        $companys = array_unique($companys);

        if (!empty($storeIds)) {
            $stores = Store::query()->select(["id", "store_id", "pid", "store_name"])->whereIn("store_id", $storeIds)->get()->toArray();
            $stores = array_combine(array_column($stores, "store_id"), $stores);
        }

        if (!empty($merchantIds)) {
            $merchants = Merchant::query()->select(["id", "name"])->whereIn("id", $merchantIds)->get()->toArray();
            $merchants = array_combine(array_column($merchants, "id"), $merchants);
        }

        if (!empty($companys)) {
            $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
            $companys = array_combine(array_column($companys, "company"), $companys);
        }

        foreach ($list as $k => $v) {
            $v = (array)$v;
            $v["store_pid_name"] = "";
            $v["store_id_name"] = "";
            $v["cashier_name"] = "";
            $v["company_name"] = "";
            if (isset($v["store_pid"]) && isset($stores[$v["store_pid"]])) {
                $v["store_pid_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            }
            if (isset($v["store_id"]) && isset($stores[$v["store_id"]])) {
                $v["store_id_name"] = $stores[$v["store_id"]]["store_name"] ?? "";
            }
            if (isset($v["cashier_id"]) && isset($merchants[$v["cashier_id"]])) {
                $v["cashier_name"] = $merchants[$v["cashier_id"]]["name"] ?? "";
            }
            if (isset($v["company"]) && isset($companys[$v["company"]])) {
                $v["company_name"] = $companys[$v["company"]]["company_desc"] ?? "";
            }
            $list[$k] = $v;
        }
        return [
            "total" => $total,
            "data" => $list
        ];
    }

    public function payTypeCount(array $where, array $agentList): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $addstoresNum = 0;
        if (!empty($agentList)) {
            $agentListIds = array_column($agentList, "id");
            $addstoresNum = Store::query()->where('pid', 0)
                ->whereIn('user_id', $agentListIds)
                ->whereBetween('created_at', [$start, $end])->count();
        }
        //统计数据
        $timeMappTables = $util->getTableBill($timeBatch);
        $count = [
            "total_fee" => 0,
            "total_num" => 0,
            "refund_fee" => 0,

            "total_fee_wx" => 0,
            "total_num_wx" => 0,
            "refund_fee_wx" => 0,

            "total_fee_zfb" => 0,
            "total_num_zfb" => 0,
            "refund_fee_zfb" => 0,

            "total_fee_ysf" => 0,
            "total_num_ysf" => 0,
            "refund_fee_ysf" => 0,

            "total_fee_sk" => 0,
            "total_num_sk" => 0,
            "refund_fee_sk" => 0,

            "total_fee_yzf" => 0,
            "total_num_yzf" => 0,
            "refund_fee_yzf" => 0,

            "profit_column" => 0,
            "fee_column" => 0,

            "receipt_amount" => 0,
            "settlement_amount" => 0

        ];
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);
            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            //支付方式 1：微信 2：支付宝 3：翼支付 4云闪付支付5 刷卡支付
            $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(total_num) as total_num"),//交易笔数
                DB::raw("sum(refund_fee) as refund_fee"),//交易笔数
                DB::raw("sum(fee_amount) as fee_amount"), //交易手续费

                DB::raw("sum(if(pay_type=1,total_fee,0)) as total_fee_wx"),//微信交易总额
                DB::raw("sum(if(pay_type=1,total_num,0)) as total_num_wx"),//微信交易总额
                DB::raw("sum(if(pay_type=1,refund_fee,0)) as refund_fee_wx"),//微信交易总额

                DB::raw("sum(if(pay_type=2,total_fee,0)) as total_fee_zfb"),//交易金额
                DB::raw("sum(if(pay_type=2,total_num,0)) as total_num_zfb"),//微信交易总额
                DB::raw("sum(if(pay_type=2,refund_fee,0)) as refund_fee_zfb"),//微信交易总额

                DB::raw("sum(if(pay_type=3,total_fee,0)) as total_fee_yzf"),//交易金额
                DB::raw("sum(if(pay_type=3,total_num,0)) as total_num_yzf"),//
                DB::raw("sum(if(pay_type=3,refund_fee,0)) as refund_fee_yzf"),//

                DB::raw("sum(if(pay_type=4,total_fee,0)) as total_fee_ysf"),//交易金额
                DB::raw("sum(if(pay_type=4,total_num,0)) as total_num_ysf"),//
                DB::raw("sum(if(pay_type=4,refund_fee,0)) as refund_fee_ysf"),//

                DB::raw("sum(if(pay_type=5,total_fee,0)) as total_fee_sk"),//交易金额
                DB::raw("sum(if(pay_type=5,total_num,0)) as total_num_sk"),//
                DB::raw("sum(if(pay_type=5,refund_fee,0)) as refund_fee_sk"),//
            ]);
            if (isset($where["profit_column"]) && !empty($where["profit_column"])) {
                $query->addSelect(DB::raw("sum(" . $where["profit_column"] . ") as profit_column"));
            }
            if (isset($where["fee_column"]) && !empty($where["fee_column"])) {
                $query->addSelect(DB::raw("sum(" . $where["fee_column"] . ") as fee_column"));
            }
            $result = $query->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["total_num"] = bcadd((string)$count["total_num"], (string)$v["total_num"], 2);//$v["refund_fee"];
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);//$v["refund_fee"];

                $count["receipt_amount"] = bcsub((string)$count["total_fee"], (string)$v["refund_fee"], 2);
                $count["settlement_amount"] = bcsub(bcsub((string)$count["total_fee"], (string)$v["fee_amount"], 2), (string)$count["refund_fee"], 2);

                $count["total_fee_wx"] = bcadd((string)$count["total_fee_wx"], (string)$v["total_fee_wx"], 2);//$v["fee_amount"];
                $count["total_num_wx"] = bcadd((string)$count["total_num_wx"], (string)$v["total_num_wx"], 2);//$v["fee_amount"];
                $count["refund_fee_wx"] = bcadd((string)$count["refund_fee_wx"], (string)$v["refund_fee_wx"], 2);//$v["fee_amount"];


                $count["total_fee_zfb"] = bcadd((string)$count["total_fee_zfb"], (string)$v["total_fee_zfb"], 2);// $v["refund_fee_nextday"];
                $count["total_num_zfb"] = bcadd((string)$count["total_num_zfb"], (string)$v["total_num_zfb"], 2);//$v["fee_amount"];
                $count["refund_fee_zfb"] = bcadd((string)$count["refund_fee_zfb"], (string)$v["refund_fee_zfb"], 2);//$v["fee_amount"];

                $count["total_fee_yzf"] = bcadd((string)$count["total_fee_yzf"], (string)$v["total_fee_yzf"], 2);
                $count["total_num_yzf"] = bcadd((string)$count["total_num_yzf"], (string)$v["total_num_yzf"], 2);//$v["fee_amount"];
                $count["refund_fee_yzf"] = bcadd((string)$count["refund_fee_yzf"], (string)$v["refund_fee_yzf"], 2);//$v["fee_amount"];

                $count["total_fee_ysf"] = bcadd((string)$count["total_fee_ysf"], (string)$v["total_fee_ysf"], 2);
                $count["total_num_ysf"] = bcadd((string)$count["total_num_ysf"], (string)$v["total_num_ysf"], 2);//$v["fee_amount"];
                $count["refund_fee_ysf"] = bcadd((string)$count["refund_fee_ysf"], (string)$v["refund_fee_ysf"], 2);//$v["fee_amount"];

                $count["total_fee_sk"] = bcadd((string)$count["total_fee_sk"], (string)$v["total_fee_sk"], 2);
                $count["total_num_sk"] = bcadd((string)$count["total_num_sk"], (string)$v["total_num_sk"], 2);//$v["fee_amount"];
                $count["refund_fee_sk"] = bcadd((string)$count["refund_fee_sk"], (string)$v["refund_fee_sk"], 2);//$v["fee_amount"];

                if (isset($where["profit_column"]) && !empty($where["profit_column"])) {
                    $count["profit_column"] = bcadd((string)$count["profit_column"], (string)$v["profit_column"], 2);
                }
                if (isset($where["fee_column"]) && !empty($where["fee_column"])) {
                    $count["fee_column"] = bcadd((string)$count["fee_column"], (string)$v["fee_column"], 2);
                }
            }
        }
        return [$count, $addstoresNum];
    }



    //在这里处理用户的级别


    //按月分润统计,存在分页从主表查询
    public function commissionCount(array $where, array $group): array
    {
        $query = DB::connection("bi")->table("bills");
        //在这里设置查询条件
        $query->whereBetween("day", [$where["startDate"], $where["endDate"]]);
        $this->setWhere($query, $where);
        $this->setGroup($query, $group);
        //嵌套查询
        $total = Bill::query()->fromSub($query, "tmp")->count();
        //设置
        $list = $query->addSelect([
//          商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
//                DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
//                DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
//                DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
//                DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
//                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
//                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
            //查询分润情况
            DB::raw("sum(" . $where["profit_column"] . ") as profit_column"),
            DB::raw("sum(" . $where["fee_column"] . ") as fee_column")
        ])
            ->orderBy("month", "desc")
            ->offset((int)(($where["page"] - 1) * $where["pagesize"]))->limit((int)$where["pagesize"])->get()->toArray();
        //company pay_type store_id receive_model
        $companys = array_unique(array_column($list, "company"));
        $storeIds = array_unique(array_column($list, "store_id"));
        $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
        $companys = array_combine(array_column($companys, "company"), $companys);
        $stores = Store::query()->whereIn("store_id", $storeIds)->get()->toArray();
        $stores = array_combine(array_column($stores, "store_id"), $stores);

        foreach ($list as $k => $v) {
            $v = (array)$v;
            if (isset($v["company"])) {
                $v["company_name"] = $companys[$v["company"] ?? ""]["company_desc"] ?? "";
            }
            if (isset($v["store_id"])) {
                $v["store_name"] = $stores[$v["store_id"] ?? ""]["store_name"] ?? "";
            }
            $v["startDate"] = $where["startDate"];
            $v["endDate"] = $where["endDate"];
            $v["receipt_fee"] = bcsub((string)$v["total_fee"], (string)$v["refund_fee"], 2);
            $list[$k] = $v;
        }
        return [
            "total" => $total,
            "data" => $list
        ];
    }




    //商户交易统计

    /**
     * @param array $where curHdIndex   startDate  endDate  agentName StoreName payType company  money cashier
     * @return void
     * @throws \Exception
     */
    public function merchantGet(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        //查询不能超过92天
        $count = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "total_num" => 0,
            "refund_num" => 0,
            "refund_fee_nextday" => 0,
            "refund_num_next" => 0,
            "coupon_amount_yc" => 0,
            "coupon_num_yc" => 0,
            "coupon_amount_mc" => 0,
            "coupon_num_mc" => 0,
        ];
//        PrintSqlService::listen();
        foreach ($timeMappTables as $table => $time) {
            list($min, $max) = $util->getYearDayRange($time);

            $query = Bill::query()->from($table);
            //在这里设置查询条件
            $this->setWhere($query, $where);
            $query->whereBetween("day", [$min, $max]);
            $result = $query->select([
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(total_num) as total_num"), //交易笔数
                DB::raw("sum(refund_num) as refund_num"),// 退款笔数
                DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
                DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
                DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
                DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
            ])
                ->get()->toArray();
            foreach ($result as $v) {
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);//$v["refund_fee"];
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);//$v["fee_amount"];
                $count["total_num"] += $v["total_num"];
                $count["refund_num"] += $v["refund_num"];
                $count["refund_fee_nextday"] = bcadd((string)$count["refund_fee_nextday"], (string)$v["refund_fee_nextday"], 2);// $v["refund_fee_nextday"];
                $count["refund_num_next"] += $v["refund_num_next"];
                $count["coupon_amount_yc"] = bcadd((string)$count["coupon_amount_yc"], (string)$v["coupon_amount_yc"], 2);
                $count["coupon_num_yc"] += $v["coupon_num_yc"];
                $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
                $count["coupon_num_mc"] += $v["coupon_num_mc"];
            }
        }

        //处理 商家实收 实际净额  今日退金额  今日退笔数
//        商家实收=交易金额-退款金额，实际净额=商家实收-结算手续费
//        $count["商家实收"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
//        $count["商家实收"] = bcsub((string)$count["商家实收"], (string)$count["coupon_amount_mc"], 2);
//        $count["实际净额"] = bcsub((string)$count["商家实收"], (string)$count["fee_amount"], 2);
//        $count["今日退金额"] = bcsub((string)$count["refund_fee"], (string)$count["refund_fee_nextday"], 2);
//        $count["今日退笔数"] = bcsub((string)$count["refund_num"], (string)$count["refund_num_next"], 2);
        $count["merchant_actual_receipt"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
        $count["merchant_actual_receipt"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["coupon_amount_mc"], 2);
        $count["merchant_actual_amount"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["fee_amount"], 2);
        $count["today_refund_amount"] = bcsub((string)$count["refund_fee"], (string)$count["refund_fee_nextday"], 2);
        $count["today_refund_num"] = bcsub((string)$count["refund_num"], (string)$count["refund_num_next"], 2);
        return $count;
    }

    public function merchantGetList(array $where): array
    {
        list($start, $end) = $this->searchTime($where);
        $query = Bill::query();
        //在这里设置查询条件
        $this->setWhere($query, $where);
        $query->whereBetween("day", [$start, $end]);
        $lists = $query->select([
            "day", "store_id", "company",
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
            DB::raw("sum(total_fee) as total_fee"),//交易金额
            DB::raw("sum(refund_fee) as refund_fee"),//退款金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(total_num) as total_num"), //交易笔数
            DB::raw("sum(refund_num) as refund_num"),// 退款笔数
            DB::raw("sum(if(refund_type=2,refund_fee,0)) as refund_fee_nextday"),//隔日退款
            DB::raw("count(if(refund_type=2,1,null)) as refund_num_next"),//当日退笔数
            DB::raw("sum(if(coupon_type=1,coupon_amount,0)) as coupon_amount_yc"),//优惠金额- 优惠类型 预充值优惠券
            DB::raw("count(if(coupon_type=1,1,null)) as coupon_num_yc"),//优惠笔数- 预充值优惠券
            DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
            DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
        ])
            ->groupBy(["day", "store_id", "company"])
            ->get()->toArray();

        $storeIds = array_unique(array_column($lists, "store_id"));
        $companys = array_unique(array_column($lists, "company"));

        $stores = Store::query()->whereIn("store_id", $storeIds)->get()->toArray();
        $stores = array_combine(array_column($stores, "store_id"), $stores);
        $companys = StoreAllPayWayList::query()->whereIn("company", $companys)->get()->toArray();
        $companys = array_combine(array_column($companys, "company"), $companys);
        //处理 商家实收 实际净额  今日退金额  今日退笔数
//        商家实收=交易金额-退款金额，实际净额=商家实收-结算手续费
//        $count["商家实收"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
//        $count["商家实收"] = bcsub((string)$count["商家实收"], (string)$count["coupon_amount_mc"], 2);
//        $count["实际净额"] = bcsub((string)$count["商家实收"], (string)$count["fee_amount"], 2);
//        $count["今日退金额"] = bcsub((string)$count["refund_fee"], (string)$count["refund_fee_nextday"], 2);
//        $count["今日退笔数"] = bcsub((string)$count["refund_num"], (string)$count["refund_num_next"], 2);
        foreach ($lists as $k => $item) {
            $item["merchant_actual_receipt"] = bcsub((string)$item["total_fee"], (string)$item["refund_fee"], 2);
            $item["merchant_actual_receipt"] = bcsub((string)$item["merchant_actual_receipt"], (string)$item["coupon_amount_mc"], 2);
            $item["merchant_actual_amount"] = bcsub((string)$item["merchant_actual_receipt"], (string)$item["fee_amount"], 2);
            $item["today_refund_amount"] = bcsub((string)$item["refund_fee"], (string)$item["refund_fee_nextday"], 2);
            $item["today_refund_num"] = bcsub((string)$item["refund_num"], (string)$item["refund_num_next"], 2);
            $item["store_name"] = $stores[$item["store_id"]]["store_name"] ?? "";
            $item["company_name"] = $companys[$item["company"]]["company_desc"] ?? "";
            $lists[$k] = $item;
        }
        return $lists;
    }

    //门店交易统计
    public function storeGet(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        $data = [];
        $countTemplate = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "total_num" => 0,
            "refund_num" => 0,
            "coupon_amount_mc" => 0,
            "coupon_num_mc" => 0,
            "wx_total_fee" => 0,
            "wx_coupon_amount_mc" => 0,
            "wx_fee_amount" => 0,
            "zfb_total_fee" => 0,
            "zfb_coupon_amount_mc" => 0,
            "zfb_fee_amount" => 0,
            "merchant_actual_receipt" => 0,
        ];
        foreach ($timeMappTables as $table => $times) {
            list($min, $max) = $util->getYearDayRange($times);
            $query = Bill::query()->from($table);
            $query->whereBetween("day", [$min, $max]);
            $this->setWhere($query, $where);
            $storeResults = $query->select([
                "store_id",
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(total_num) as total_num"), //交易笔数
                DB::raw("sum(refund_num) as refund_num"),// 退款笔数
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
                DB::raw("count(if(coupon_type=2,1,null)) as coupon_num_mc"),//优惠笔数- 免充值优惠券
                DB::raw("sum(if(pay_type=1,total_fee,0)) as wx_total_fee"),//微信支付总金额
                DB::raw("sum(if(coupon_type=2 && pay_type=1,coupon_amount,0)) as wx_coupon_amount_mc"),//微信优惠金额- 优惠类型 免充值优惠券
                DB::raw("sum(if(pay_type=1,fee_amount,0)) as wx_fee_amount"),//微信支付手续费
                DB::raw("sum(if(pay_type=2,total_fee,0)) as zfb_total_fee"),//支付宝支付总金额
                DB::raw("sum(if(coupon_type=2 && pay_type=2,coupon_amount,0)) as zfb_coupon_amount_mc"),//zfb优惠金额- 优惠类型 免充值优惠券
                DB::raw("sum(if(pay_type=2,fee_amount,0)) as zfb_fee_amount"),//支付宝支付手续费
                //支付宝
            ])
                ->groupBy(["store_id"])
                ->get()->toArray();

            foreach ($storeResults as $v) {
                $count = $data[$v["store_id"]] ?? $countTemplate;
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);
                $count["total_num"] += $v["total_num"];
                $count["refund_num"] += $v["refund_num"];
                $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
                $count["coupon_num_mc"] += $v["coupon_num_mc"];
                $count["wx_total_fee"] = bcadd((string)$count["wx_total_fee"], (string)$v["wx_total_fee"], 2);
                $count["wx_coupon_amount_mc"] = bcadd((string)$count["wx_coupon_amount_mc"], (string)$v["wx_coupon_amount_mc"], 2);
                $count["wx_fee_amount"] = bcadd((string)$count["wx_fee_amount"], (string)$v["wx_fee_amount"], 2);
                $count["zfb_total_fee"] = bcadd((string)$count["zfb_total_fee"], (string)$v["zfb_total_fee"], 2);
                $count["zfb_coupon_amount_mc"] = bcadd((string)$count["zfb_coupon_amount_mc"], (string)$v["zfb_coupon_amount_mc"], 2);
                $count["zfb_fee_amount"] = bcadd((string)$count["zfb_fee_amount"], (string)$v["zfb_fee_amount"], 2);
                $count["merchant_actual_receipt"] = bcsub((string)$count["total_fee"], (string)$count["refund_fee"], 2);
                $count["merchant_actual_receipt"] = bcsub((string)$count["merchant_actual_receipt"], (string)$count["coupon_amount_mc"], 2);
                $data[$v["store_id"]] = $count;
            }
        }
        return [$data, $countTemplate];
    }

    //渠道统计
    public function companyGet(array $where): array
    {
        $util = new TimeUtil();
        list($start, $end) = $this->searchTime($where);
        // 商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
        //按照时间批量查询
        $timeBatch = $util->rangeTimeYear($start, $end);
        //选择对应的表
        $timeMappTables = $util->getTableBill($timeBatch);
        //查询不能超过92天
        $countTemplate = [
            "total_fee" => 0,
            "refund_fee" => 0,
            "fee_amount" => 0,
            "coupon_amount_mc" => 0,
        ];
        $data = [];
        foreach ($timeMappTables as $table => $times) {
            list($min, $max) = $util->getYearDayRange($times);
            $query = Bill::query()->from($table);
            $query->whereBetween("day", [$min, $max]);
//            $this->setWhere($query, $where);
            $storeCompanyResults = $query->select([
                "store_id", "company",
//                商家实收 实际净额   交易金额/笔数   退款金额/笔数【隔日退+今日退】 手续费  会员支付金额  优惠金额/笔数
                DB::raw("sum(total_fee) as total_fee"),//交易金额
                DB::raw("sum(refund_fee) as refund_fee"),//退款金额
                DB::raw("sum(fee_amount) as fee_amount"),//手续费
                DB::raw("sum(if(coupon_type=2,coupon_amount,0)) as coupon_amount_mc"),//优惠金额- 优惠类型 免充值优惠券
            ])
                ->groupBy(["store_id", "company"])
                ->get()->toArray();
            foreach ($storeCompanyResults as $v) {
                $count = $data[$v["store_id"]][$v["company"]] ?? $countTemplate;
                $count["total_fee"] = bcadd((string)$count["total_fee"], (string)$v["total_fee"], 2);
                $count["refund_fee"] = bcadd((string)$count["refund_fee"], (string)$v["refund_fee"], 2);
                $count["fee_amount"] = bcadd((string)$count["fee_amount"], (string)$v["fee_amount"], 2);
                $count["coupon_amount_mc"] = bcadd((string)$count["coupon_amount_mc"], (string)$v["coupon_amount_mc"], 2);
                $data[$v["store_id"]][$v["company"]] = $count;
            }
        }
        return [$data, $countTemplate];
    }


    //获取代理商的数据

    public function get()
    {

        $where = [];

        $timeUtil = new TimeUtil();
        list($start, $end, $desc) = $timeUtil->get(2);
        $query = Bill::query()->from($timeUtil->getBillLogTableName($start));
        $bill = $query->select([
            "day", "agent_id", "store_id", "receive_model", "ways_type", "pay_type",
            DB::raw("sum(total_fee) as total_fee"),//总金额
            DB::raw("sum(fee_amount) as fee_amount"),//手续费
            DB::raw("sum(fee_commission) as fee_commission"),//分润金额
            DB::raw("sum(refund_fee) as refund_fee"),//总金额
            DB::raw("sum(oem_profit) as oem_profit"),//oem收益
            DB::raw("sum(oem_fee) as oem_fee"), //oem实收
            DB::raw("sum(agent1_profit) as agent1_profit"),//1级代理商收益
            DB::raw("sum(agent1_fee) as agent1_fee"),//1级代理商实收
            DB::raw("sum(agent2_profit) as agent2_profit"),//2级代理商收益
            DB::raw("sum(agent2_fee) as agent2_fee"),//2级代理商实收
            DB::raw("sum(agent3_profit) as agent3_profit"),//3级代理商收益
            DB::raw("sum(agent3_fee) as agent3_fee"),//3级代理商实收
            DB::raw("sum(agent4_profit) as agent4_profit"),//4级代理商收益
            DB::raw("sum(agent4_fee) as agent4_fee"),//4级代理商实收
        ])
            //合并数据
            ->where($where)
            ->groupBy([
                "day", "agent_id", "store_id", "receive_model", "ways_type", "pay_type"
            ])
            ->get()->toArray();
        //合并数据
        $load = new LoadRelationDataService();
        list($bill, $modelCollection) = $load->mergeData($bill, [1, 2, 3, 4]);
        unset($modelCollection);
        return $bill;
    }


    //"receipt_amount" => 0,//实收  总收款-退款
    //趋势图变化
    public function pstoreTrend(array $where, bool $page = true): array
    {
        if ($where["type"] == 1) {
            list($startAfter, $endAfter) = $this->searchTime($where);
            list($startBefore, $endBefore) = $this->searchTimeBefore($where);
        }
        $query = DB::connection("bi")->table("bills");
        //开始构建sql
//        PrintSqlService::listen();
        //当前阶段的
        $queryAfter = clone $query;
        $queryAfter->select(["store_pid", DB::raw("sum(total_fee)-sum(refund_fee) AS after_receipt_amount")]);
        if ($where["type"] == 1) {
            $queryAfter->whereBetween("day", [$startAfter, $endAfter]);
        } else if ($where["type"] == 2) {
            $queryAfter->where("month", $where["month"]);
        }
        $this->setWhere($queryAfter, $where);
        $queryAfter->groupBy(["store_pid"]);

        //上一个阶段的
        $queryBefore = clone $query;
        $queryBefore->select(["store_pid", DB::raw("sum(total_fee)-sum(refund_fee) AS before_receipt_amount")]);
        if ($where["type"] == 1) {
            $queryBefore->whereBetween("day", [$startBefore, $endBefore]);
        } else if ($where["type"] == 2) {
            $queryBefore->where("month", date("Ym", strtotime("-1 year", strtotime($where["month"] . "01 00:00:00"))));
        }
        $this->setWhere($queryBefore, $where);
        $queryBefore->groupBy(["store_pid"]);

        //组装2个临时表sql  join 做好对应关系
        $queryTmp = clone $query;
        $queryTmp->fromSub($queryAfter, env("BI_DB_DATABASE") . "_after_tmp")->leftJoinSub($queryBefore, env("BI_DB_DATABASE") . "_before_tmp", "after_tmp.store_pid", "=", "before_tmp.store_pid");

        $queryTmp->select([
            "after_tmp.store_pid",
            "after_receipt_amount",
            "before_receipt_amount",
            //计算差额
            DB::raw("after_receipt_amount-COALESCE(before_receipt_amount,0) as trend_amount"),
            //计算比例
            DB::raw("(after_receipt_amount-COALESCE(before_receipt_amount,0))/IF(before_receipt_amount IS NULL OR before_receipt_amount=0, 1, before_receipt_amount)* 100 AS trend_rate ")
        ]);

        $query = Bill::query()->fromSub($queryTmp, env("BI_DB_DATABASE") . "_tmp");
        $query->where(function ($query) use ($where) {
//            "trend_rate_min" => $request->get("trend_rate_min", -100000),
//                "trend_rate_max" => $request->get("trend_rate_max", -100000),
            if ($where["trend_rate_min"] != null && $where["trend_rate_min"] != -100000) {
                $query->where("trend_rate", ">=", $where["trend_rate_min"]);
            }
            if ($where["trend_rate_max"] != null && $where["trend_rate_max"] != -100000) {
                $query->where("trend_rate", "<=", $where["trend_rate_max"]);
            }
        });
        //在分析好的数据临时表中做查询 和分页+排序
        if ($page) {
            $total = $query->count();
            $list = $query->limit($where["pagesize"])->offset(($where["page"] - 1) * $where["pagesize"])->orderBy("trend_rate", "desc")->get()->toArray();
        } else {
            $total = 0;
            $list = $query->orderBy("trend_rate", "desc")->get()->toArray();
        }

        $storePids = array_column($list, "store_pid");
        $stores = Store::query()->whereIn("store_id", $storePids)->get()->toArray();
        //在这里格式化数据
        $stores = array_combine(array_column($stores, "store_id"), $stores);

        foreach ($list as $k => $v) {
            $v["store_name"] = $stores[$v["store_pid"]]["store_name"] ?? "";
            //34.375000
            $v["trend_rate"] = round($v["trend_rate"], 3) . "%";
            if (empty($v["before_receipt_amount"])) {
                $v["before_receipt_amount"] = 0;
            }
            $list[$k] = $v;
        }
        return [
            "total" => $total,
            "data" => $list
        ];
    }

//{
//"store_pid": "2022521113330588581",
//"after_receipt_amount": "117871.70",
//"before_receipt_amount": "78194.60",
//"trend_amount": "39677.10",
//"trend_rate": "50.741%",
//"store_name": "阜南县悦惠购物超市"
//},
    public function pstoreTrendExport(array $where): string
    {
        $result = $this->pstoreTrend($where, false);
        $data = $result["data"];
        $title = [
            "主店名称", "当前有效金额", "对比有效金额", "增长金额", "增长比例"
        ];
        $value = [];
        //写入execl
        foreach ($data as $k => $v) {
            $value[] = [
                $v["store_name"],
                $v["after_receipt_amount"],
                $v["before_receipt_amount"],
                $v["trend_amount"],
                $v["trend_rate"],
            ];
        }
        //写入excel
        $util = new ExeclUtil();
        $fileName = "增长趋势" . md5(json_encode($where)) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/pstoreTrendExport/" . date("Y/md", time()) . "/");
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $util->exportToExcel($filePath . $fileName, [], $title, $value);
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        //删除fileName
        return $url;
    }


    public function agentTrend(array $where, bool $page = true): array
    {
        $agent_id_column = $where["agent_id_column"];
        list($startAfter, $endAfter) = $this->searchTime($where);
        list($startBefore, $endBefore) = $this->searchTimeBefore($where);
        $query = DB::connection("bi")->table("bills");
        //开始构建sql
//        PrintSqlService::listen();
        //当前阶段的
        $queryAfter = clone $query;
        $queryAfter->select([$agent_id_column, DB::raw("sum(total_fee)-sum(refund_fee) AS after_receipt_amount")]);
        $queryAfter->whereBetween("day", [$startAfter, $endAfter]);
        $this->setWhere($queryAfter, $where);
        $queryAfter->groupBy([$agent_id_column]);

        //上一个阶段的
        $queryBefore = clone $query;
        $queryBefore->select([$agent_id_column, DB::raw("sum(total_fee)-sum(refund_fee) AS before_receipt_amount")]);
        $queryBefore->whereBetween("day", [$startBefore, $endBefore]);
        $this->setWhere($queryBefore, $where);
        $queryBefore->groupBy([$agent_id_column]);

        //组装2个临时表sql  join 做好对应关系
        $queryTmp = clone $query;
        $queryTmp->fromSub($queryAfter, env("BI_DB_DATABASE") . "_after_tmp")->leftJoinSub($queryBefore, env("BI_DB_DATABASE") . "_before_tmp", "after_tmp." . $agent_id_column, "=", "before_tmp." . $agent_id_column);

        $queryTmp->select([
            "after_tmp." . $agent_id_column,
            "after_receipt_amount",
            "before_receipt_amount",
            //计算差额
            DB::raw("after_receipt_amount-COALESCE(before_receipt_amount,0) as trend_amount"),
            //计算比例
            DB::raw("(after_receipt_amount-COALESCE(before_receipt_amount,0))/IF(before_receipt_amount IS NULL OR before_receipt_amount=0, 1, before_receipt_amount)* 100 AS trend_rate ")
        ]);

        $query = Bill::query()->fromSub($queryTmp, env("BI_DB_DATABASE") . "_tmp");
        $query->where(function ($query) use ($where) {
//            "trend_rate_min" => $request->get("trend_rate_min", -100000),
//                "trend_rate_max" => $request->get("trend_rate_max", -100000),
            if ($where["trend_rate_min"] != null && $where["trend_rate_min"] != -100000) {
                $query->where("trend_rate", ">=", $where["trend_rate_min"]);
            }
            if ($where["trend_rate_max"] != null && $where["trend_rate_max"] != -100000) {
                $query->where("trend_rate", "<=", $where["trend_rate_max"]);
            }
        });

        if ($page) {
            $total = $query->count();
            //在分析好的数据临时表中做查询 和分页+排序
            $list = $query->limit($where["pagesize"])->offset(($where["page"] - 1) * $where["pagesize"])->orderBy("trend_rate", "desc")->get()->toArray();
        } else {
            $total = 0;
            $list = $query->orderBy("trend_rate", "desc")->get()->toArray();
        }

        //获取代理商信息
        $agentIds = array_column($list, $agent_id_column);
        $agent = User::query()->whereIn("id", $agentIds)->get()->toArray();
        $agent = array_combine(array_column($agent, "id"), $agent);

        foreach ($list as $k => $v) {
            //34.375000
            $v["agent_id_val"] = $v[$agent_id_column];
            $v["agent_name"] = $agent[$v["agent_id_val"]]["name"] ?? "";
            $v["trend_rate"] = round($v["trend_rate"], 3) . "%";
            if (empty($v["before_receipt_amount"])) {
                $v["before_receipt_amount"] = 0;
            }
            $list[$k] = $v;
        }


        return [
            "total" => $total,
            "data" => $list
        ];
    }


    public function agentTrendExport(array $where): string
    {
        $result = $this->agentTrend($where, false);
        $data = $result["data"];
        $title = [
            "代理商名称", "当前有效金额", "对比有效金额", "增长金额", "增长比例"
        ];
        $value = [];
        //写入execl
        foreach ($data as $k => $v) {
            $value[] = [
                $v["agent_name"],
                $v["after_receipt_amount"],
                $v["before_receipt_amount"],
                $v["trend_amount"],
                $v["trend_rate"],
            ];
        }
        //写入excel
        $util = new ExeclUtil();
        $fileName = "增长趋势" . md5(json_encode($where)) . "_" . time() . ".csv";
        $filePath = public_path("/uploads/csv/agentTrendExport/" . date("Y/md", time()) . "/");
        if (!is_dir($filePath)) {
            mkdir($filePath, 0777, true);
        }
        $util->exportToExcel($filePath . $fileName, [], $title, $value);
        $url = $util->uploadOss($filePath . $fileName, $fileName);
        //删除文件
        if (file_exists($filePath . $fileName)) {
            unlink($filePath . $fileName);
        }
        //删除fileName
        return $url;
    }
}